﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookStore.Common;
using System.Data.SqlClient;
using BookStore.Models;
using System.Data;

namespace BookStore.Controllers
{
    public class BillDetailController : Controller
    {
        private static BillDetailController _controller = null;
        public static BillDetailController getController()
        {
            if (_controller == null)
                return new BillDetailController();
            else
                return _controller;
        }
        public BillDetailController()
        {
            _controller = this;
        }


        public void InsertBillDetail(BillDetail bill)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_InsertBillDetail", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@BillID", bill.Fk_BillID);
                cmd.Parameters.AddWithValue("@ProductID", bill.Fk_ProductID);
                cmd.Parameters.AddWithValue("@SellingQuantity", bill.SellingQuantity);
                cmd.Parameters.AddWithValue("@AmountOfBill", bill.AmountOfBill);
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void SetQuantityBillDetail(BillDetail bill)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_SetQuantityBillDetail", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@BillID", bill.Fk_BillID);
                cmd.Parameters.AddWithValue("@ProductID", bill.Fk_ProductID);
                cmd.Parameters.AddWithValue("@SellingQuantity", bill.SellingQuantity);
                cmd.Parameters.AddWithValue("@AmountOfBill", bill.AmountOfBill);
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        internal int getBillDetailID(String ProductName, String BillID)
        {
            DataTable dt = new DataTable("GetBillDetailID");
            con = BSCommon.getConnection();
            int temp = 0;
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetBillDetailID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = ProductName;
                cmd.Parameters.Add("@BillID", SqlDbType.Int).Value = BillID;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                temp = Convert.ToInt32(dt.Rows[0][0].ToString());
            }
            catch (Exception e)
            {
               // throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return temp;
        }

        internal DataTable getBillDetailByID(int BillID)
        {
            DataTable dt = new DataTable("BillDetail");
            string sql = @"SELECT  dbo.PRODUCT.ProductName, dbo.PRODUCT.SellingPrice, dbo.BILLDETAIL.SellingQuantity, dbo.BILLDETAIL.AmountOfBill
                                    FROM  dbo.BILL INNER JOIN
                                            dbo.BILLDETAIL ON dbo.BILL.BillID = dbo.BILLDETAIL.BillID INNER JOIN
                                            dbo.PRODUCT ON dbo.BILLDETAIL.ProductID = dbo.PRODUCT.ProductID
                                    WHERE   dbo.BILLDETAIL.BillID = " + BillID;
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
                //throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        internal void removeBillDetailByID(int BillDetailID)
        {
            string sql = @"Delete from BillDetail where BillDetailID = " + BillDetailID;
            con = BSCommon.getConnection();
            try
            {                
                con.Open();
                tran = con.BeginTransaction();                
                cmd = new SqlCommand(sql, con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }
    }
}
